﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Threading;
using Microsoft.SqlServer.Management.Smo;
using SqlServerDRControlPanel.MirroringGroup;


namespace AutomatedDatabaseDR
{
    
    class MirroringGroup
    {
        
        #region Properties
        //Properties
        Object LockingObject = new Object();

        private string m_PricipleServerName;
        public string PrincipleServerName
        {
            set { m_PricipleServerName = value; }
        }
        
        private string m_MirrorServerName;
        public string MirrorServerName
        {
            set { m_MirrorServerName = value; }
        }
        
        private bool m_ThreadComplete;
        public bool ThreadIsComplete
        {
            get { return m_ThreadComplete; }
        }
               
        private int m_RowNumber;
        private List<String> m_PollListList;
        private string m_ThreadStatus;
        private string m_databaseName;
        public List<String> returnDic;
        private List<String> m_PostDrErrors = new List<string>();
        public event EventHandler<MirroringGroupEventArgs> MirroringGroupEvents;
        public event EventHandler<MirroringGroupCompletedEventArgs> MirroringGroupCompletedEvent;
        private const int m_LoopTimeout = 3000;
        private const int m_SleepInterval = 100;
        private const int m_ThreadEnterTimeout = 1000;
        private SqlServerDRControlPanel.Logging.Logging log;
        #endregion

        #region Constructor

        public MirroringGroup() 
        {

        }

        public MirroringGroup(List<string> serverListToPoll)
        {
            m_PollListList = serverListToPoll;
        }

        public MirroringGroup(string database, int rowNumber)
        {
            m_databaseName = database;
            m_RowNumber = rowNumber;
            log = new SqlServerDRControlPanel.Logging.Logging();
        }
        

        #endregion

        #region Poll Current Status
        /// <summary>
        /// This method uses List<> PollList to generate current status of all databases in the 
        /// datagrid. This method is used by the threaded poll servers.
        /// </summary>
        /// <returns></returns>
        public void GetCurrentMirroringStatusAll()
        {
            
            string rowNumber = "";
            returnDic = new List<String>();
            foreach (string s in m_PollListList)
            {
                string[] workerList = s.Split('|');
                try
                {
                    Server principleServer = new Server(workerList[0]);
                    Database pricipleDatabase = principleServer.Databases[workerList[1]];
                    Database QueryDb = principleServer.Databases["MASTER"];
                    rowNumber = workerList[2].ToString();

                    if (pricipleDatabase.IsMirroringEnabled)
                    {
                        string SQL = "select mirroring_role_desc from sys.database_mirroring where database_id = db_id('" + workerList[1] + "')";
                        string result = QueryDb.ExecuteWithResults(SQL).Tables[0].Rows[0][0].ToString();
                        if (result == "PRINCIPAL")
                        {
                            returnDic.Add(workerList[0] + "|True|" + rowNumber);
                        }
                        else
                        {
                            returnDic.Add(workerList[0] + "|False|" + rowNumber);
                        }
                    }
                    else
                    {
                        returnDic.Add(workerList[0] + "|NotMirrored|" + rowNumber);
                    }

                }
                catch (NullReferenceException)
                {
                    returnDic.Add(workerList[0] + "|ERROR database does not exist|" + rowNumber);
                }
                catch (Exception e)
                {
                    //TODO this is not parsed correctly at the main app.
                    // there is a bug here when app cannot connect to db servers.
                    returnDic.Add(workerList[0] + "|ERROR " + e.Message + "|" + rowNumber);
                }
            }
            m_ThreadComplete = true;
            DoEvent("PollComplete");

        }

        /// <summary>
        /// 
        /// </summary>
        public void GetCurrentMirroringStatus(string principleServer, string databaseName)
        {

            string queryText = string.Format("select mirroring_role_desc from sys.database_mirroring where database_id = db_id('{0}')"
                , databaseName);

            m_ThreadComplete = true;
            DoEvent("PollComplete");

        }

        #endregion

        #region Remove Mirroring
        /// <summary>
        /// Method to remove database mirroring if it is enabled on the server.
        /// </summary>
        public void RemoveMirroring()
        {
            int loopCounter = 0;

            try
            {
                m_ThreadStatus = "RemoveMirroring() Started";
                DoEvent(m_ThreadStatus);
                Server smoServer = new Server(m_PricipleServerName);
                Database smoDatabase = smoServer.Databases[m_databaseName];
                Server mirrorServer = new Server(m_MirrorServerName);
                // Check to see if the mirror has a db snapshot, if so drop it.
                if (mirrorServer.Databases[m_databaseName].IsDatabaseSnapshotBase)
                {
                    DropDbSnapshot(mirrorServer, m_databaseName);
                }
                // HACK: using tsql because its simpler than SMO
                string restoreDBScript = "RESTORE DATABASE " + m_databaseName + " WITH RECOVERY";

                if (smoDatabase.IsMirroringEnabled)
                {
                    m_ThreadStatus = "Remove Mirror Principle";
                    DoEvent(m_ThreadStatus);
                    smoDatabase.ChangeMirroringState(MirroringOption.Off);
                    
                    // DatabaseStatus should be restoring before continuing on to the restore/recovery
                    // Wait for the database to be not mirrored and have a status of restoring
                    
                    while (!DbIsreadyForRecovery(m_MirrorServerName, m_databaseName))
                    {
                        m_ThreadStatus = "Waiting for mirror to become ready.";
                        DoEvent(m_ThreadStatus);
                        System.Threading.Thread.Sleep(m_SleepInterval);
                        loopCounter++;
                        if (loopCounter == m_LoopTimeout)
                        {
                            m_ThreadStatus = "DbIsreadyForRecovery() exceeded loop count.";
                            DoEvent(m_ThreadStatus);
                            return;
                        }
                    }
                    loopCounter = 0;
                    m_ThreadStatus = "Recover Mirror database";
                    DoEvent(m_ThreadStatus);
                    
                    // TODO call out to another method for the restore... So I can run the 
                    // remove more than once.
                    // TODO wrap this call into a while loop to make sure the db is recovered.
                    // RecoverDatabase(restoreDBScript);
                    mirrorServer.Databases["MASTER"].ExecuteNonQuery(restoreDBScript);

                    
                }
                
                m_ThreadStatus = "Database recovered";
                DoEvent(m_ThreadStatus);

                m_ThreadStatus = "Waiting for the recovery process";
                DoEvent(m_ThreadStatus);
                while (GetDbStatus(m_MirrorServerName, m_databaseName) != "Normal")
                {
                    System.Threading.Thread.Sleep(m_SleepInterval); 
                    loopCounter++;
                    if (loopCounter == m_LoopTimeout)
                    {
                        m_ThreadStatus = "GetDbStatus() exceeded loop count.";
                        DoEvent(m_ThreadStatus);
                        return;
                    }
                }
                m_ThreadStatus = "Generating post actions";
                DoEvent(m_ThreadStatus);
                
                int rowCounter = 0;
                foreach (DataRow row in GetPostDrScripts().Rows)
                {
                    rowCounter++;
                    ExecuteDrAction(row[1].ToString());
                    m_ThreadStatus = "Running script: " + rowCounter + " of " + 
                        GetPostDrScripts().Rows.Count;
                    DoEvent(m_ThreadStatus);
                }
                if (m_PostDrErrors.Count > 0)
                {
                    m_ThreadStatus = "CAUTION " + rowCounter + " Post Script(s) run. " + 
                        m_PostDrErrors.Count + " post script(s) failed.";
                    DoEvent(m_ThreadStatus);
                }
                else
                {
                    m_ThreadStatus = "COMPLETE " + rowCounter + " Post Script(s) run.";
                    DoEvent(m_ThreadStatus);
                }
                DoCompletedEvent(m_RowNumber);
            }
            catch (Exception e)
            {
                string error = e.Message;
                m_ThreadStatus = "ERROR: " + error;
                log.WriteSystemLogEntry(e.Message);
                DoEvent(m_ThreadStatus);
                DoCompletedEvent(m_RowNumber);
            }
        }

        

        #endregion

        #region Failover Databases
        /// <summary>
        /// Perform mirror failover, handles both safety off and high safety mirroring.
        /// </summary>
        public void FailoverDatabase()
        {
            m_ThreadStatus = "Starting FailoverDatabase()";
            DoEvent(m_ThreadStatus);
            Server principleServer;
            Server mirrorServer;
            Database principleDatabase;
            Database mirroredDatabase;
            int loopCounter = 0;
            try
            {
                principleServer = new Server(m_PricipleServerName);
                mirrorServer = new Server(m_MirrorServerName);
                principleDatabase = principleServer.Databases[m_databaseName];
                mirroredDatabase = mirrorServer.Databases[m_databaseName];

                if (principleDatabase.IsMirroringEnabled)
                {
                    // Check to see if the mirror has a db snapshot, if so drop it.
                    if (mirrorServer.Databases[m_databaseName].IsDatabaseSnapshotBase)
                    {
                        DropDbSnapshot(mirrorServer, m_databaseName);
                    }
                    // If not high safety mirroring, then we have to set the safetly level
                    // to high before we can failover.
                    if (principleDatabase.MirroringSafetyLevel == MirroringSafetyLevel.Off)
                    {
                        m_ThreadStatus = "Setting high safety";
                        DoEvent(m_ThreadStatus);
                        principleDatabase.MirroringSafetyLevel = MirroringSafetyLevel.Full;
                        principleDatabase.Alter();
                        // loop here to make sure the safety level is full before we failover.
                        // this is not an instantanious action.
                        while (!PrincipleIsSynchronized(m_PricipleServerName, m_databaseName))
                        {
                            Thread.Sleep(m_SleepInterval);
                            loopCounter++;
                            if (loopCounter == m_LoopTimeout)
                            {
                                m_ThreadStatus = "Setting high safety exceeded loop count.";
                                DoEvent(m_ThreadStatus);
                                return;
                            }
                        }
                        m_ThreadStatus = "Setting Failover";
                        DoEvent(m_ThreadStatus);
                        principleDatabase.ChangeMirroringState(MirroringOption.Failover);
                        
                        // Now connect to mirror and set back to async...
                        m_ThreadStatus = "Setting safety off";
                        DoEvent(m_ThreadStatus);
                        mirroredDatabase.MirroringSafetyLevel = MirroringSafetyLevel.Off;
                        mirroredDatabase.Alter();
                    }
                    // This is used only of high safety mirroring is used.
                    else
                    {
                        principleDatabase.ChangeMirroringState(MirroringOption.Failover);
                    }
                }
                else
                {
                    m_ThreadStatus = "Database Not mirrored";
                    DoEvent(m_ThreadStatus);
                    return;
                }


                // Now that the server is failed over, connect to DR and run any post failover commands.
                // These commands will be stored in the configuration database.
                m_ThreadStatus = "Generating post actions";
                DoEvent(m_ThreadStatus);
                

                int rowCounter = 0;
                foreach (DataRow row in GetPostDrScripts().Rows)
                {
                    rowCounter++;
                    m_ThreadStatus = "Running script: " + rowCounter + " of " + GetPostDrScripts().Rows.Count;
                    DoEvent(m_ThreadStatus);
                    ExecuteDrAction(row[1].ToString());
                }
                m_ThreadStatus = "COMPLETE " + rowCounter + " Post Script(s) run.";
                DoEvent(m_ThreadStatus);
                DoCompletedEvent(m_RowNumber);
            }
            catch (Exception e)
            {
                string error = e.Message;
                m_ThreadStatus = "ERROR: " + error;
                log.WriteSystemLogEntry(e.Message);
                DoEvent(m_ThreadStatus);
                DoCompletedEvent(m_RowNumber);
            }
        }

        
        #endregion

        #region Force Mirror online Allow data loss
        /// <summary>
        /// This is used for situation when Principle is not available. 
        /// This method uses the ForceFailoverAndAllowDataLoss methods.
        /// </summary>
        public void ForceMirrorOnline()
        {
            int loopCounter = 0;
            try
            {
                m_ThreadStatus = "Starting Cut to DR";
                DoEvent(m_ThreadStatus);
                Server mirrorServer = new Server(m_MirrorServerName);
                Database mirrorDatabase = mirrorServer.Databases[m_databaseName];
                if (mirrorDatabase.IsMirroringEnabled)
                {
                    // Check to see if the mirror has a db snapshot, if so drop it.
                    if (mirrorServer.Databases[m_databaseName].IsDatabaseSnapshotBase)
                    {
                        DropDbSnapshot(mirrorServer, m_databaseName);
                    }
                    m_ThreadStatus = "Force Mirror online";
                    DoEvent(m_ThreadStatus);
                    mirrorDatabase.ChangeMirroringState(MirroringOption.ForceFailoverAndAllowDataLoss);

                    // getting stuck in this loop... because mirroring never suspends.
                    // RemoveMirroring() uses != Normal. need to test what status' 
                    // comes back.
                    while (GetMirroringStatus(m_MirrorServerName, m_databaseName) != "Suspended")
                    {
                        Thread.Sleep(m_SleepInterval);
                        m_ThreadStatus = "Waiting for mirror to suspend";
                        loopCounter++;
                        if (loopCounter == m_LoopTimeout)
                        {
                            m_ThreadStatus = "RemoveMirrorBringUpDr() exceeded loop count.";
                            DoEvent(m_ThreadStatus);
                            return;
                        }
                    }
                    mirrorDatabase.ChangeMirroringState(MirroringOption.Off);
                }
                // Now that the server is failed over, connect to DR and run any post failover commands.
                // These commands will be stored in the configuration database.
                m_ThreadStatus = "Generating post actions";
                DoEvent(m_ThreadStatus);
                
                int rowCounter = 0;
                foreach (DataRow row in GetPostDrScripts().Rows)
                {
                    rowCounter++;
                    m_ThreadStatus = "Running script: " + rowCounter + " of " + GetPostDrScripts().Rows.Count;
                    DoEvent(m_ThreadStatus);
                    ExecuteDrAction(row[1].ToString());
                }
                m_ThreadStatus = "COMPLETE " + rowCounter + " Post Script(s) run.";
                DoEvent(m_ThreadStatus);
                DoCompletedEvent(m_RowNumber);
            }
            catch (Exception e)
            {
                string error = e.Message;
                m_ThreadStatus = "ERROR: " + error;
                log.WriteSystemLogEntry(e.Message);
                DoEvent(m_ThreadStatus);
                DoCompletedEvent(m_RowNumber);
            }
        }

        #endregion

        #region Private Methods

        /// <summary>
        /// This method should be called at the end to ensure that we null out all objects
        /// so it can be collected.
        /// </summary>
        private void ExitThread()
        {
            // todo: call this method...
            m_ThreadStatus = null;
            m_databaseName = null;
            m_MirrorServerName = null;
            m_PricipleServerName = null;
            LockingObject = null;
            m_PollListList = null;
            m_PostDrErrors = null;
        }

        /// <summary>
        /// This method checks to see if the mirror has a database snapshot active on the mirror
        /// and if so drops it.
        /// </summary>
        private void DropDbSnapshot(Server SmoServer, string databaseName)
        {
            DoEvent("Dropping mirror snapshot.");
            foreach (Database d in SmoServer.Databases)
            {
                if (d.DatabaseSnapshotBaseName == databaseName)
                {
                    d.Drop();
                    break;
                }
            }

        }

        /// <summary>
        /// Get the status of mirroring and return as a string. This method uses the
        /// monitor object to lock the synchronize the threads.
        /// </summary>
        /// <param name="ServerName"></param>
        /// <param name="DatabaseName"></param>
        /// <returns></returns>
        private string GetMirroringStatus(string serverName, string databaseName)
        {
            string retStatus = "";
            try
            {
                while (!Monitor.TryEnter(LockingObject, m_ThreadEnterTimeout))
                {
                    m_ThreadStatus = "waiting on threadlock";
                }
                Server mirrorServer = new Server(serverName);
                Database mirrorDatabase = mirrorServer.Databases[databaseName];
                retStatus = mirrorDatabase.MirroringStatus.ToString();
                return retStatus;
            }
            finally
            {
                Monitor.Exit(LockingObject);
            }
        }

        /// <summary>
        /// Checks the principle database to ensure that it is synchronized
        /// and ready for failover.
        /// </summary>
        /// <param name="principleServer"></param>
        /// <param name="principleDatabase"></param>
        /// <returns></returns>
        private bool PrincipleIsSynchronized(string principleServer, string principleDatabase)
        {
            Server svr = new Server(principleServer);
            Database db = svr.Databases[principleDatabase];
            if (db.MirroringStatus == MirroringStatus.Synchronized)
            {
                return true;
            }
            else { return false; }
        }

        /// <summary>
        /// Recover Database
        /// </summary>
        /// <param name="restoreDBScript"></param>
        private void RecoverDatabase(string restoreDBScript)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// Local method to send events to the event handler.
        /// </summary>
        /// <param name="val"></param>
        private void DoEvent(string val)
        {
            // Copy to a temporary variable to be thread-safe.
            EventHandler<MirroringGroupEventArgs> tempEvent = MirroringGroupEvents;
            string eventData = val;
            int rowNumber = m_RowNumber;
            if (tempEvent != null)
                tempEvent(this, new MirroringGroupEventArgs(rowNumber, eventData));
        }

        /// <summary>
        /// Send Completed event
        /// </summary>
        /// <param name="val"></param>
        private void DoCompletedEvent(int val)
        {
            EventHandler<MirroringGroupCompletedEventArgs> tempEvent = MirroringGroupCompletedEvent;
            int eventData = val;
            if (tempEvent != null)
                tempEvent(this, new MirroringGroupCompletedEventArgs(eventData));
        }

        /// <summary>
        /// Execute each postdr script separately so if there is an exception
        /// we dont stop processing the entire batch.
        /// </summary>
        /// <param name="Query"></param>
        private void ExecuteDrAction(string query)
        {
            try
            {
                Server DRServer = new Server(m_MirrorServerName);
                Database DRDatabase = DRServer.Databases[m_databaseName];
                DRDatabase.ExecuteNonQuery(query);
            }
            catch (Exception e)
            {
                m_PostDrErrors.Add(e.Message);
            }
        }

        /// <summary>
        /// This method gets the current status of the database and returns a string value
        /// of the database state.
        /// </summary>
        /// <param name="server"></param>
        /// <param name="databasename"></param>
        /// <returns></returns>
        private string GetDbStatus(string server, string databaseName)
        {
            // Adding locking because SMO is not thread safe? Jr 07/21/08
            try
            {
                m_ThreadStatus = "GetDbStatus() try and get threadlock";
                DoEvent(m_ThreadStatus);
                while ( !Monitor.TryEnter(LockingObject, m_ThreadEnterTimeout) )
                {
                    // nothing
                }
                Server smoServer = new Server(server);
                Database db = smoServer.Databases[databaseName];
                string status = db.Status.ToString();
                return status;
            }
            catch (Exception e)
            {
                return e.Message;
            }
            finally
            {
                Monitor.Exit(LockingObject);
            }
        }

        /// <summary>
        /// Bool, verify that the database is in a state ready for recovery.
        /// </summary>
        /// <param name="server"></param>
        /// <param name="databasename"></param>
        /// <returns></returns>
        private bool DbIsreadyForRecovery(string server, string databaseName)
        {
            // Adding locking because SMO is not thread safe? Jr 07/21/08
            try
            {
                while ( !Monitor.TryEnter(LockingObject, m_ThreadEnterTimeout) )
                {
                    // nothing
                }
                Server SmoServer = new Server(server);
                Database db = SmoServer.Databases[databaseName];
                if (db.IsMirroringEnabled)
                {
                    return false;
                }
                if (db.Status.ToString().ToUpper(CultureInfo.InvariantCulture).Contains("RESTORING"))
                {
                    return true;
                }
                else { return false; }
            }
            finally
            {
                Monitor.Exit(LockingObject);
            }
        }

        /// <summary>
        /// Get PostDrActions as a datatable.
        /// </summary>
        /// <returns></returns>
        private DataTable GetPostDrScripts()
        {
            ConfigItems ci = new ConfigItems();
            string configServer = ConfigurationManager.AppSettings["ConfigDbConn"];
            string[] ConfigItems = configServer.Split('|');
            ci.ConfigDB = ConfigItems[1];
            ci.ConfigServer = @ConfigItems[0];
            DataTable dt;
            
            try
            {
                dt = ci.GetPostActionScript(m_databaseName);
                return dt;
            }
            catch (Exception e)
            {
                m_ThreadStatus = "Error encountered in FaiOverActions.GetPostDrActions()" + e.Message;
                return dt = new DataTable();
            }
        }

        #endregion

        
    } // Class end
} // NS end
